package edu.cdbi.database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import edu.cdbi.model.instance.Exam;

public class ExamManagement {

	public ExamManagement() {
		
	}
	
	public Integer insert(int courseId, int studentId, int grade){
		Connection connection = DatabaseManager.getInstance().createConnection();
		if(connection == null){
			return null;
		}
		
		try {
			PreparedStatement statement = connection.prepareStatement(
					"INSERT INTO exam (course_id, student_id, grade) VALUES(?, ?, ?)");
			statement.setInt(1, courseId);
			statement.setInt(2, studentId);
			statement.setInt(3, grade);
			statement.executeUpdate();
			
			statement = connection.prepareStatement("SELECT @@identity");
			ResultSet resultSet = statement.executeQuery();
			resultSet.next();
			int number = resultSet.getInt(1);
			statement.close();
			statement.close();
			connection.close();
			return number;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
	
	public List<Exam> getList(){
		Connection connection = DatabaseManager.getInstance().createConnection();
		if(connection == null){
			return null;
		}
		try {
			PreparedStatement statement = connection.prepareStatement(
					"SELECT exam_id, course_id, student_id, grade FROM exam");
			ResultSet resultSet = statement.executeQuery();
			List<Exam> list = new ArrayList<Exam>();
			while(resultSet.next()){
				Exam exam = new Exam();
				exam.setId(resultSet.getInt("exam_id"));
				exam.setCourseId(resultSet.getInt("course_id"));
				exam.setStudentId(resultSet.getInt("student_id"));
				exam.setGrade(resultSet.getInt("grade"));
				list.add(exam);
			}
			resultSet.close();
			statement.close();
			connection.close();
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
	
	public boolean delete(int id) {
		Connection connection = DatabaseManager.getInstance()
				.createConnection();
		if (connection == null) {
			return false;
		}
		try {
			PreparedStatement statement = connection
					.prepareStatement("DELETE FROM exam WHERE exam_id=" + id);
			statement.executeUpdate();
			statement.close();
			connection.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
}
